----- Cap nhat so du bang tb0106 tu phieu nhap so du
GO
DROP PROCEDURE [dbo].[pr0065]
GO
CREATE  PROCEDURE [dbo].[pr0065]
	 @DivisionID varchar(20), 
	@WareHouseID  varchar(20),
	 @TranMonth  int, 
	@TranYear int, 
	@InventoryID  varchar(20), 
	@ConvertedAmount as Decimal(28,8), 
	@ConvertedQuantity as Decimal(28,8), 
	@DebitAccountID  varchar(20), 
	@CreditAccountID  varchar(20), 
	@Type as tinyint -- (1) la cap nhat tang,(0 ) la cap nhat giam

 AS


If @Type =1	 --- Cap nhat tang (Inserted)
	Begin
		
		If not exists (Select 1 From tb0106 	Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity ,  DebitAmount , CreditQuantity ,
					CreditAmount,  EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@DebitAccountID, @ConvertedQuantity, @ConvertedAmount , 0,0, 0,0, 
				@ConvertedQuantity, @ConvertedAmount)
		else
			Update tb0106
				Set	BeginQuantity 	=	Isnull(BeginQuantity,0)	+ isnull(@ConvertedQuantity,0),
					BeginAmount 	=	Isnull(BeginAmount,0)	+ isnull(@ConvertedAmount,0),
					EndQuantity		=	Isnull(EndQuantity,0)		+ Isnull(@ConvertedQuantity,0),
					EndAmount		=	Isnull(EndAmount,0)		+Isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
					DivisionID =@DivisionID and
					WareHouseID =@WareHouseID and
					InventoryAccountID =@DebitAccountID and
					TranMonth =@TranMonth and
					TranYear =@TranYear	
	

	End

Else  		--- Cap nhat giam (Deleted)
	Begin
	If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity,  DebitAmount,  CreditQuantity,
					CreditAmount,   EndQuantity , EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@DebitAccountID, -@ConvertedQuantity, -@ConvertedAmount, 0,0, 0,0,  
				-@ConvertedQuantity, -@ConvertedAmount)
		else
			Update tb0106
				set 	BeginQuantity	 	=	Isnull(BeginQuantity,0)		- Isnull(@ConvertedQuantity,0),
					BeginAmount 		=	Isnull(BeginAmount,0)		- Isnull(@ConvertedAmount,0),
					EndQuantity		=	Isnull(EndQuantity,0)		- Isnull(@ConvertedQuantity,0),
					EndAmount		= 	Isnull(EndAmount,0)	- 	Isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID  =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	End


GO